﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GFMS.MODEL;
using KPIS.GERP.GFMS.IDAL;

namespace KPIS.GERP.GFMS.DAL
{
    public sealed class D_FIM_Petitions : IDAL_FIM_Petitions
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
        ConvertNullable cn = new ConvertNullable();
        

        public D_FIM_Petitions()
        {

        }

        public IList<M_FIM_Petitions> GetFull(Nullable<int> bgm_source_seq, Nullable<int> bgm_year)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Petitions> infoList = new List<M_FIM_Petitions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);

                strSQL = "SELECT pet.petition_seq, pet.bgm_source_seq, pet.bgm_year, pet.petition_date"
                    + ", pet.ref_no, pet.direct_pay, pet.petition_type, pet.tran_grp_seq, pet.remarks"
                    + ", pet.petition_no, pet.real_petition_date"
                    + ", petd.bgm_exp_grp_seq, s.bgm_source_name"
                    + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                        + " ELSE 'ขอเบิกงบอุดหนุน'"
                        + " END AS petition_type_name"
                    + ", petd.sum_amount, petd.sum_amount_tax, petd.sum_amount_penalty"
                    + ", petd.sum_amount_redemption, petd.sum_amount_fund, petd.sum_amount_real"

                    + ", r.receive_date"

                    + ", pet.approved_by, pet.approved_when"
                    + ", pet.record_status, rs.record_status_name"
                    + ", pet.created_by, pet.created_when, pet.updated_by, pet.updated_when"

                    + " FROM fim_petitions pet"
                    + " LEFT JOIN (SELECT petd.petition_seq, mr.bgm_exp_grp_seq"
                        + ", SUM(COALESCE(petd.amount, 0)) AS sum_amount"
                        + ", SUM(COALESCE(petd.amount_tax, 0)) AS sum_amount_tax"
                        + ", SUM(COALESCE(petd.amount_penalty, 0)) AS sum_amount_penalty"
                        + ", SUM(COALESCE(petd.amount_redemption, 0)) AS sum_amount_redemption"
                        + ", SUM(COALESCE(petd.amount_fund, 0)) AS sum_amount_fund"
                        + ", SUM(COALESCE(petd.amount_real, 0)) AS sum_amount_real"
                        + " FROM fim_petition_dtls petd"
                        + " LEFT JOIN fim_money_requests mr ON petd.money_req_seq = mr.money_req_seq"
                        + " WHERE petd.record_status not like @delete_status"
                        + " GROUP BY petd.petition_seq, mr.bgm_exp_grp_seq"
                        + ") petd ON pet.petition_seq = petd.petition_seq"
                    + " LEFT JOIN bgm_mas_source s ON pet.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN fim_receives r ON pet.petition_seq = r.petition_seq"
                    + " LEFT JOIN sys_record_status rs ON pet.record_status = rs.record_status"
                    + " WHERE pet.record_status not like 'D'"
                    + (bgm_source_seq != null ? " AND pet.bgm_source_seq = @bgm_source_seq" : "")
                    + (bgm_year != null ? " AND pet.bgm_year = @bgm_year" : "")
                    + " ORDER BY pet.petition_date";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Petitions info = new M_FIM_Petitions();
                        info.petition_seq = Convert.ToInt32(drc[i]["petition_seq"]);
                        info.bgm_source_seq = cn.ConvertNullInt(drc[i]["bgm_source_seq"]);
                        info.bgm_year = cn.ConvertNullInt(drc[i]["bgm_year"]);
                        info.petition_date = cn.ConvertNullDateTime(drc[i]["petition_date"]);
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.direct_pay = drc[i]["direct_pay"].ToString();
                        info.petition_type = drc[i]["petition_type"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.petition_no = drc[i]["petition_no"].ToString();
                        info.petition_date = cn.ConvertNullDateTime(drc[i]["real_petition_date"]);

                        info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[i]["bgm_exp_grp_seq"]);
                        info.bgm_source_name = drc[i]["bgm_source_name"].ToString();
                        info.petition_type_name = drc[i]["petition_type_name"].ToString();
                        info.sum_amount = cn.ConvertNullDouble(drc[i]["sum_amount"]);
                        info.sum_amount_tax = cn.ConvertNullDouble(drc[i]["sum_amount_tax"]);
                        info.sum_amount_penalty = cn.ConvertNullDouble(drc[i]["sum_amount_penalty"]);
                        info.sum_amount_redemption = cn.ConvertNullDouble(drc[i]["sum_amount_redemption"]);
                        info.sum_amount_fund = cn.ConvertNullDouble(drc[i]["sum_amount_fund"]);
                        info.sum_amount_real = cn.ConvertNullDouble(drc[i]["sum_amount_real"]);
                
                        info.approved_by = cn.ConvertNullInt(drc[i]["approved_by"]);
                        info.approved_when = cn.ConvertNullDateTime(drc[i]["approved_when"]);
                        info.tran_grp_seq = cn.ConvertNullInt(drc[i]["tran_grp_seq"]);

                        info.record_status = drc[i]["record_status"].ToString();
                        info.record_status_name = drc[i]["record_status_name"].ToString();
                        info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                        info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                        info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                        info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Petitions> GetList(Nullable<int> bgm_source_seq, Nullable<int> bgm_year
            , string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Petitions> infoList = new List<M_FIM_Petitions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    //+ ", concat(coalesce(pet.ref_no, '') , ' : ', coalesce(pet.petition_type_name, '')) as petition_type_name"
                    + " FROM (SELECT pet.petition_seq, pet.ref_no"
                        + ", concat(cast(coalesce(pet.petition_no, '') as varchar) , ' : '"
                            + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                                + " ELSE 'ขอเบิกงบอุดหนุน'"
                                + " END) as petition_type_name"
                        //+ ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                        //    + " ELSE 'ขอเบิกงบอุดหนุน'"
                        //    + " END AS petition_type_name"
                        + " FROM fim_petitions pet"
                        + " WHERE pet.record_status not like @delete_status"
                        + (bgm_source_seq != null ? " AND pet.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND pet.bgm_year = @bgm_year" : "")
                        + ") pet"
                    + (filter != null ? " WHERE pet.petition_type_name = @filter" : "")
                    + " ORDER BY pet.petition_type_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Petitions info = new M_FIM_Petitions();
                        info.petition_seq = Convert.ToInt32(drc[i]["petition_seq"]);
                        info.petition_type_name = drc[i]["petition_type_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Petitions> GetDirectPaymentList(Nullable<int> bgm_source_seq
            , Nullable<int> bgm_year, Nullable<int> petition_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Petitions> infoList = new List<M_FIM_Petitions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@petition_seq", petition_seq);

                strSQL = "SELECT *"
                    //+ ", concat(coalesce(pet.ref_no, '') , ' : ', coalesce(pet.petition_type_name, '')) as petition_type_name"
                    + " FROM (SELECT pet.petition_seq, pet.ref_no"
                        + ", concat(cast(coalesce(pet.petition_no, '') as varchar) , ' : '"
                            + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                                + " ELSE 'ขอเบิกงบอุดหนุน'"
                                + " END) as petition_type_name"
                    //+ ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                    //    + " ELSE 'ขอเบิกงบอุดหนุน'"
                    //    + " END AS petition_type_name"
                        + " FROM fim_petitions pet"
                        + " WHERE pet.record_status not like @delete_status"
                        + (bgm_source_seq != null ? " AND pet.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND pet.bgm_year = @bgm_year" : "")
                        + " and pet.direct_pay = '1'"
                        + " and pet.petition_seq not in"
                            + " (select coalesce(petition_seq, 0) as petition_seq"
                            + " from fim_payments"
                            + " where record_status not like @delete_status"
                            + (petition_seq != null ? " and petition_seq not like @petition_seq" : "")
                            + ")"
                        + ") pet"
                    //+ (filter != null ? " WHERE pet.petition_type_name = @filter" : "")
                    + " ORDER BY pet.petition_type_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Petitions info = new M_FIM_Petitions();
                        info.petition_seq = Convert.ToInt32(drc[i]["petition_seq"]);
                        info.petition_type_name = drc[i]["petition_type_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public IList<M_FIM_Petitions> GetApprovedList(Nullable<int> bgm_source_seq, Nullable<int> bgm_year
            , string filter)
        {
            string strSQL;
            DataRowCollection drc = null;
            IList<M_FIM_Petitions> infoList = new List<M_FIM_Petitions>();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@approve_status", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(1, "@bgm_source_seq", bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", bgm_year);
                dbManager.AddParameters(3, "@filter", "%" + filter + "%");

                strSQL = "SELECT *"
                    //+ ", concat(coalesce(pet.ref_no, '') , ' : ', coalesce(pet.petition_type_name, '')) as petition_type_name"
                    + " FROM (SELECT pet.petition_seq, pet.ref_no"
                        + ", concat(cast(coalesce(pet.petition_no, '') as varchar) , ' : '"
                            + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                                + " ELSE 'ขอเบิกงบอุดหนุนและงบรายจ่ายอื่นๆ'"
                                + " END) as petition_type_name"
                    //+ ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                    //    + " ELSE 'ขอเบิกงบอุดหนุน'"
                    //    + " END AS petition_type_name"
                        + " FROM fim_petitions pet"
                        + " WHERE pet.record_status = @approve_status"
                        + (bgm_source_seq != null ? " AND pet.bgm_source_seq = @bgm_source_seq" : "")
                        + (bgm_year != null ? " AND pet.bgm_year = @bgm_year" : "")
                        + ") pet"
                    + (filter.Length > 0 ? " WHERE pet.petition_type_name like @filter" : "")
                    + " ORDER BY pet.petition_type_name";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_FIM_Petitions info = new M_FIM_Petitions();
                        info.petition_seq = Convert.ToInt32(drc[i]["petition_seq"]);
                        info.petition_type_name = drc[i]["petition_type_name"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public M_FIM_Petitions GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_FIM_Petitions info = new M_FIM_Petitions();
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@seq", seq);

                strSQL = "SELECT pet.petition_seq, pet.bgm_source_seq, pet.bgm_year, pet.petition_date"
                    + ", pet.ref_no, pet.direct_pay, pet.petition_type, pet.tran_grp_seq, pet.remarks"
                    + ", pet.petition_no, pet.real_petition_date"
                    + ", petd.bgm_exp_grp_seq, s.bgm_source_name"
                    + ", concat(cast(coalesce(pet.petition_no, '') as varchar) , ' : '"
                            + ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                                + " ELSE 'ขอเบิกงบอุดหนุน'"
                                + " END) as petition_type_name"
                    //+ ", CASE WHEN pet.petition_type = '0' THEN 'ขอเบิกจากรายการรอเบิก'"
                    //    + " ELSE 'ขอเบิกงบอุดหนุน'"
                    //    + " END AS petition_type_name"
                    + ", petd.sum_amount, petd.sum_amount_tax, petd.sum_amount_penalty"
                    + ", petd.sum_amount_redemption, petd.sum_amount_fund, petd.sum_amount_real"

                    + ", pet.approved_by, pet.approved_when"
                    + ", pet.record_status, rs.record_status_name"
                    + ", pet.created_by, pet.created_when, pet.updated_by, pet.updated_when"

                    + " FROM fim_petitions pet"
                    + " LEFT JOIN (SELECT petd.petition_seq, mr.bgm_exp_grp_seq"
                        + ", SUM(COALESCE(petd.amount, 0)) AS sum_amount"
                        + ", SUM(COALESCE(petd.amount_tax, 0)) AS sum_amount_tax"
                        + ", SUM(COALESCE(petd.amount_penalty, 0)) AS sum_amount_penalty"
                        + ", SUM(COALESCE(petd.amount_redemption, 0)) AS sum_amount_redemption"
                        + ", SUM(COALESCE(petd.amount_fund, 0)) AS sum_amount_fund"
                        + ", SUM(COALESCE(petd.amount_real, 0)) AS sum_amount_real"
                        + " FROM fim_petition_dtls petd"
                        + " LEFT JOIN fim_money_requests mr ON petd.money_req_seq = mr.money_req_seq"
                        + " WHERE petd.record_status not like @delete_status"
                        + " GROUP BY petd.petition_seq, mr.bgm_exp_grp_seq"
                        + ") petd ON pet.petition_seq = petd.petition_seq"
                    + " LEFT JOIN bgm_mas_source s ON pet.bgm_source_seq = s.bgm_source_seq"
                    + " LEFT JOIN sys_record_status rs ON pet.record_status = rs.record_status"
                    + " WHERE pet.petition_seq = @seq";
                

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.petition_seq = Convert.ToInt32(drc[0]["petition_seq"]);
                    info.bgm_source_seq = cn.ConvertNullInt(drc[0]["bgm_source_seq"]);
                    info.bgm_year = cn.ConvertNullInt(drc[0]["bgm_year"]);
                    info.petition_date = cn.ConvertNullDateTime(drc[0]["petition_date"]);
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.direct_pay = drc[0]["direct_pay"].ToString();
                    info.petition_type = drc[0]["petition_type"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.petition_no = drc[0]["petition_no"].ToString();
                    info.petition_date = cn.ConvertNullDateTime(drc[0]["real_petition_date"]);

                    info.bgm_exp_grp_seq = cn.ConvertNullInt(drc[0]["bgm_exp_grp_seq"]);
                    info.bgm_source_name = drc[0]["bgm_source_name"].ToString();
                    info.petition_type_name = drc[0]["petition_type_name"].ToString();
                    info.sum_amount = cn.ConvertNullDouble(drc[0]["sum_amount"]);
                    info.sum_amount_tax = cn.ConvertNullDouble(drc[0]["sum_amount_tax"]);
                    info.sum_amount_penalty = cn.ConvertNullDouble(drc[0]["sum_amount_penalty"]);
                    info.sum_amount_redemption = cn.ConvertNullDouble(drc[0]["sum_amount_redemption"]);
                    info.sum_amount_fund = cn.ConvertNullDouble(drc[0]["sum_amount_fund"]);
                    info.sum_amount_real = cn.ConvertNullDouble(drc[0]["sum_amount_real"]);

                    info.approved_by = cn.ConvertNullInt(drc[0]["approved_by"]);
                    info.approved_when = cn.ConvertNullDateTime(drc[0]["approved_when"]);
                    info.tran_grp_seq = cn.ConvertNullInt(drc[0]["tran_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.record_status_name = drc[0]["record_status_name"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        //public string SelectRecentInsertedRoleSeq()
        //{
        //    string strSQL;
        //    string strResult;

        //    try
        //    {
        //        dbManager.Open();

        //        strSQL = "select scope_identity() as last_seq";

        //        strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
        //    }
        //    catch
        //    {
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.Dispose();
        //    }

        //    return strResult;
        //}

        public string Insert(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(14);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@petition_date", info.petition_date);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@direct_pay", info.direct_pay);
                dbManager.AddParameters(6, "@petition_type", info.petition_type);
                dbManager.AddParameters(7, "@approved_by", info.approved_by);
                dbManager.AddParameters(8, "@approved_when", info.approved_when);
                dbManager.AddParameters(9, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(10, "@remarks", info.remarks);

                dbManager.AddParameters(11, "@record_status", SystemInfo._WorkRecordStatus);
                dbManager.AddParameters(12, "@created_by", info.created_by);
                dbManager.AddParameters(13, "@created_when", info.created_when);

                strSQL = "INSERT INTO fim_petitions"
                    + "(bgm_source_seq, bgm_year, petition_date, ref_no, direct_pay"
                    + ", petition_type, approved_by, approved_when, tran_grp_seq, remarks"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@bgm_source_seq"
                        + ", @bgm_year"
                        + ", @petition_date"
                        + ", @ref_no"
                        + ", @direct_pay"
                        + ", @petition_type"
                        + ", @approved_by"
                        + ", @approved_when"
                        + ", @tran_grp_seq"
                        + ", @remarks"

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when);"
                    + " select scope_identity();";
                var strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();
                return strSeq;
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(13);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@bgm_source_seq", info.bgm_source_seq);
                dbManager.AddParameters(2, "@bgm_year", info.bgm_year);
                dbManager.AddParameters(3, "@petition_date", info.petition_date);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@direct_pay", info.direct_pay);
                dbManager.AddParameters(6, "@petition_type", info.petition_type);
                dbManager.AddParameters(7, "@approved_by", info.approved_by);
                dbManager.AddParameters(8, "@approved_when", info.approved_when);
                dbManager.AddParameters(9, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(10, "@remarks", info.remarks);

                dbManager.AddParameters(11, "@updated_by", info.updated_by);
                dbManager.AddParameters(12, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_petitions"
                    + " set bgm_source_seq = @bgm_source_seq"
                    + ", bgm_year = @bgm_year"
                    + ", petition_date = @petition_date"
                    + ", ref_no = @ref_no"
                    + ", direct_pay = @direct_pay"
                    + ", petition_type = @petition_type"
                    + ", approved_by = @approved_by"
                    + ", approved_when = @approved_when"
                    + ", tran_grp_seq = @tran_grp_seq"
                    + ", remarks = @remarks"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_petitions"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                strSQL = "UPDATE fim_petition_dtls"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateGlTranSeq(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@tran_grp_seq", info.tran_grp_seq);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_petitions"
                    + " SET tran_grp_seq = @tran_grp_seq"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdatePetitionNo(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@petition_no", info.petition_no);
                dbManager.AddParameters(2, "@real_petition_date", info.real_petition_date);
                dbManager.AddParameters(3, "@updated_by", info.updated_by);
                dbManager.AddParameters(4, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_petitions"
                    + " SET petition_no = @petition_no"
                    + ", real_petition_date = @real_petition_date"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void ChangeStatus(M_FIM_Petitions info)
        {
            string strSQL;
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@petition_seq", info.petition_seq);
                dbManager.AddParameters(1, "@record_status", info.record_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE fim_petitions"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE petition_seq = @petition_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }
    }
}
